/*
	EXEC SP_TRANSACTION_REPORT 0, 9, 2008
*/
ALTER PROC SP_TRANSACTION_REPORT ( @BASE_PRODUCT_ID INT,
				@MONTH INT,
				@YEAR INT )
AS
BEGIN
	DECLARE @PREV_MONTH_END_DATE DATETIME
	DECLARE @TILL_MONTH_END_DATE DATETIME	
	 
	SET @TILL_MONTH_END_DATE = CONVERT(VARCHAR,@MONTH)+ '/01/'+ CONVERT(VARCHAR,@YEAR) + ' 12:00AM ' --CAST('''' + CONVERT(VARCHAR,@MONTH) + '''/01/''' + CONVERT(VARCHAR,@YEAR) + '''' AS DATETIME) --starting day of given month
	SET @PREV_MONTH_END_DATE = DATEADD(second,-1,@TILL_MONTH_END_DATE) --previous month end day
	SET @TILL_MONTH_END_DATE = DATEADD(Month,1,@TILL_MONTH_END_DATE) --next month starting day
	SET @TILL_MONTH_END_DATE = DATEADD(second,-1,@TILL_MONTH_END_DATE) --given month end day
	
	/*IF ( @MONTH = MONTH(CURRENT_TIMESTAMP) AND @YEAR = YEAR(CURRENT_TIMESTAMP) )
	BEGIN
		SET @TILL_MONTH_END_DATE = CURRENT_TIMESTAMP
	END*/

	--PRINT @PREV_MONTH_END_DATE
	--PRINT @TILL_MONTH_END_DATE
	DECLARE @RESULTS_TABLE TABLE ( 
					DB_STARTING_WIP INT,
					FAB_SHIPMENTS_WIP INT,
					DB_ENDING_WIP INT,
					DB_TOT_DIE_MOVED_TO_ASSEMBLY_WIP INT,
					ASM_STARTING_WIP INT,
					ASM_ENDING_WIP INT,
					ACTUAL_ASM_RECEIVINGS INT,
					TOT_ASM_SHIPMENTS INT,
					ACTUAL_ASM_SHIPMENTS INT,
					FT_STARTING_WIP INT,
					ASM_SHIPMENTS_TO_FT INT,
					FT_ENDING_WIP INT,
					ACTUAL_FT_RECEIVINGS INT,
					TOT_FT_SHIPMENTS INT,
					FT_SHIPMENTS_TO_TR INT,
					FT_SHIPMENTS_TO_FG INT,
					ACTUAL_FT_SHIPMENTS INT,
					TR_STARTING_WIP INT,
					FG_SHIPMENTS_TO_TR INT,
					TR_ENDING_WIP INT,
					TOT_TR_SHIPMENTS INT,
					ACTUAL_TR_SHIPMENTS INT,
					FG_STARTING_WIP INT,
					TR_SHIPMENTS_TO_FG INT,
					FG_ENDING_WIP INT,
					TOT_FG_SHIPMENTS INT,
					SHIPMENTS_TO_CUSTOMERS INT,
					ACTUAL_FG_SHIPMENTS INT
				)

	CREATE TABLE #PREV_MTH_END_WIP_RESULTS ( PROCESSSTEP_ID INT, LOCATION VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
			LOT_CATEGORY VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT,	DATE_IN DATETIME, STAGE_NAME VARCHAR(50), 
			STAGE_POSITION VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), EXPECTED_DATE_OUT DATETIME, AGE INT,
			OUTLOOK_DATE DATETIME, GROSS_DIE INT,WAFER_ID VARCHAR(200), 
			PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT,
			PS_TYPE VARCHAR(50),PS_TYPE_ID INT,THINKNESS VARCHAR(50),GRIND VARCHAR(50),
			SL_FLOW VARCHAR(50), SL_TEST_PROGRAM VARCHAR(50),SL_HOLD_DATE DATETIME,SL_TESTER VARCHAR(50),
			MASK VARCHAR(50),LABLE_NAME VARCHAR(50),PACKAGE_TYPE VARCHAR(50)
		 )
	CREATE TABLE #TILL_MTH_END_WIP_RESULTS ( PROCESSSTEP_ID INT, LOCATION VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
			LOT_CATEGORY VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT,	DATE_IN DATETIME, STAGE_NAME VARCHAR(50), 
			STAGE_POSITION VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), EXPECTED_DATE_OUT DATETIME, AGE INT,
			OUTLOOK_DATE DATETIME, GROSS_DIE INT,WAFER_ID VARCHAR(200), 
			PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT,
			PS_TYPE VARCHAR(50),PS_TYPE_ID INT,THINKNESS VARCHAR(50),GRIND VARCHAR(50),
			SL_FLOW VARCHAR(50), SL_TEST_PROGRAM VARCHAR(50),SL_HOLD_DATE DATETIME,SL_TESTER VARCHAR(50),
			MASK VARCHAR(50),LABLE_NAME VARCHAR(50),PACKAGE_TYPE VARCHAR(50)
		 )

	

	DECLARE @TMP VARCHAR(20)
	SET @TMP = CONVERT(VARCHAR,@PREV_MONTH_END_DATE,101)
	INSERT INTO #PREV_MTH_END_WIP_RESULTS 
		EXEC sp_wip_info_as_of_date '0','0','0','0',@TMP,'0','all',''

	IF ( @MONTH = MONTH(CURRENT_TIMESTAMP) AND @YEAR = YEAR(CURRENT_TIMESTAMP) ) --CURRENT WIP
	BEGIN
		--SET @TILL_MONTH_END_DATE = CURRENT_TIMESTAMP
		INSERT INTO #TILL_MTH_END_WIP_RESULTS
			EXEC sp_wip_info_report '0','0','0','0','0','0','all','','0' --IN result set we have ps_type_name instead of ps_type, take care
	END
	ELSE
	BEGIN
		SET @TMP = CONVERT(VARCHAR,@TILL_MONTH_END_DATE,101)
		INSERT INTO #TILL_MTH_END_WIP_RESULTS
			EXEC sp_wip_info_as_of_date '0','0','0','0',@TMP,'0','all',''
	END

	--EXEC sp_wip_info_as_of_date '0','0','0','0','11/16/2007','0','all',''
	DECLARE @PREV_MTH_END_DB_WIP INT
	DECLARE @TILL_MTH_END_DB_WIP INT
	DECLARE @PREV_MTH_END_ASSEMBLY_WIP INT
	DECLARE @TILL_MTH_END_ASSEMBLY_WIP INT
	DECLARE @ASSEMBLY_RECEIVINGS_WIP INT
	DECLARE @ACTUAL_ASSEMBLY_SHIPMENTS_WIP INT
	DECLARE @PREV_MTH_END_FT_WIP INT
	DECLARE @TILL_MTH_END_FT_WIP INT
	DECLARE @PREV_MTH_END_TR_WIP INT
	DECLARE @TILL_MTH_END_TR_WIP INT
	DECLARE @PREV_MTH_END_FG_WIP INT
	DECLARE	@TILL_MTH_END_FG_WIP INT
	DECLARE @SHIPMENTS_TO_CUSTOMER INT
	DECLARE @FAB_SHIPMENTS_WIP INT
	DECLARE @TOT_DIE_MOVED_TO_ASSEMBLY INT
	DECLARE @DB_SHIPMENTS INT
	DECLARE @TOT_ASSEMBLY_SHIPMENTS INT
	DECLARE @ACTUAL_FT_RECEIVINGS_WIP INT
	DECLARE @TOT_FT_SHIPMENTS INT
	DECLARE @ACTUAL_ASSEMBLY_SHIPMENTS_TO_FT_WIP INT
	DECLARE @FT_SHIPMENTS_TO_TR_WIP INT
	DECLARE @FT_SHIPMENTS_TO_FG_WIP INT
	DECLARE @ACTUAL_FT_SHIPMENTS INT
	DECLARE @BULK_SHIPMENTS_TO_TR_WIP INT
	DECLARE @TOT_TR_SHIPMENTS INT
	DECLARE @ACTUAL_TR_SHIPMENTS_WIP INT
	DECLARE @TR_SHIPMENTS_TO_FG_WIP INT
	DECLARE @FG_SHIPMENTS_TO_TR_WIP INT
	DECLARE @TOT_FG_SHIPMENTS INT
	DECLARE @ACTUAL_FG_SHIPMENTS INT
	DECLARE @BASE_PRODUCT_ID_STR VARCHAR(10)
	
	IF ( @BASE_PRODUCT_ID IS NULL OR @BASE_PRODUCT_ID <= 0 )
	BEGIN
		SET @BASE_PRODUCT_ID_STR = '%'
	END
	ELSE
	BEGIN
		SET @BASE_PRODUCT_ID_STR = CONVERT(VARCHAR, @BASE_PRODUCT_ID)
	END
	
	
	--DIE BANK
	SELECT @PREV_MTH_END_DB_WIP = SUM(QTY_IN) FROM #PREV_MTH_END_WIP_RESULTS WHERE PS_TYPE = 'Die Bank' AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SELECT @TILL_MTH_END_DB_WIP = SUM(QTY_IN) FROM #TILL_MTH_END_WIP_RESULTS WHERE PS_TYPE = 'Die Bank' AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SELECT @FAB_SHIPMENTS_WIP=SUM(QTY_IN) FROM LOT_TABLE LT, PROCESS_STEP PS
			WHERE PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Die Bank'
				AND LT.FAB_LOT_NUMBER IN (
				SELECT DISTINCT FAB_LOT_NUMBER FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
						WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
						AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
						AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
						AND PS.PS_TYPE_NAME = 'FAB' 
						AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
						AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
							)

	SET @TOT_DIE_MOVED_TO_ASSEMBLY = ISNULL(@PREV_MTH_END_DB_WIP,0)+ISNULL(@FAB_SHIPMENTS_WIP,0)-ISNULL(@TILL_MTH_END_DB_WIP,0)
	

	--ASSEMBLY
	SELECT @PREV_MTH_END_ASSEMBLY_WIP = SUM(QTY_IN) FROM #PREV_MTH_END_WIP_RESULTS WHERE PS_TYPE = 'ASSEMBLY' AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SET @DB_SHIPMENTS = @TOT_DIE_MOVED_TO_ASSEMBLY
	SELECT @TILL_MTH_END_ASSEMBLY_WIP = SUM(QTY_IN) FROM #TILL_MTH_END_WIP_RESULTS WHERE PS_TYPE = 'ASSEMBLY' AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SELECT @ASSEMBLY_RECEIVINGS_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Die Bank'
				AND SL.SL_NEXT_PROCESS <> 'DIE BANK' 
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR

	SET @TOT_ASSEMBLY_SHIPMENTS = ISNULL(@PREV_MTH_END_ASSEMBLY_WIP,0)+ISNULL(@DB_SHIPMENTS,0)-ISNULL(@TILL_MTH_END_ASSEMBLY_WIP,0)
	SELECT @ACTUAL_ASSEMBLY_SHIPMENTS_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
				AND PS.PS_TYPE = 'ASSEMBLY'
				AND SL.SL_NEXT_PROCESS <> 'ASSEMBLY' 
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	
	--FT
	SELECT @PREV_MTH_END_FT_WIP = SUM(QTY_IN) FROM #PREV_MTH_END_WIP_RESULTS WHERE PS_TYPE = 'FT' AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SELECT @TILL_MTH_END_FT_WIP = SUM(QTY_IN) FROM #TILL_MTH_END_WIP_RESULTS WHERE PS_TYPE = 'FT' AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SELECT @ACTUAL_ASSEMBLY_SHIPMENTS_TO_FT_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'ASSEMBLY'
				AND SL.SL_NEXT_PROCESS = 'FT' 
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	
	SELECT @ACTUAL_FT_RECEIVINGS_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PRODUCT P--, PROCESS_STEP PS 
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				--AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				--AND PS.PS_TYPE = 'ASSEMBLY'
				AND P.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND SL.SL_NEXT_PROCESS = 'FT' 
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR

	SET @TOT_FT_SHIPMENTS = ISNULL(@PREV_MTH_END_FT_WIP,0)+ISNULL(@ACTUAL_ASSEMBLY_SHIPMENTS_TO_FT_WIP,0)-ISNULL(@TILL_MTH_END_FT_WIP,0)

	SELECT @FT_SHIPMENTS_TO_TR_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P  
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE'
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
				AND PS.PS_TYPE = 'FT'
				AND SL.SL_NEXT_PROCESS = 'Tape & Reel' 
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SELECT @FT_SHIPMENTS_TO_FG_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
				AND PS.PS_TYPE = 'FT'
				AND SL.SL_NEXT_PROCESS = 'FG' 
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SELECT @ACTUAL_FT_SHIPMENTS = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
				AND PS.PS_TYPE = 'FT'
				AND SL.SL_NEXT_PROCESS <> 'FT'
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR

	--Tape & Reel
	SELECT @PREV_MTH_END_TR_WIP = SUM(QTY_IN) FROM #PREV_MTH_END_WIP_RESULTS WHERE (PS_TYPE = 'Tape & Reel' OR PS_TYPE = 'Class Store') AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SELECT @TILL_MTH_END_TR_WIP = SUM(QTY_IN) FROM #TILL_MTH_END_WIP_RESULTS WHERE (PS_TYPE = 'Tape & Reel' OR PS_TYPE = 'Class Store') AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	--FT Shipments to TR = @FT_SHIPMENTS_TO_TR_WIP
	SELECT @BULK_SHIPMENTS_TO_TR_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P  
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
				AND PS.PS_TYPE = 'FG'
				AND SL.SL_NEXT_PROCESS = 'Tape & Reel' 
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR

	SET @TOT_TR_SHIPMENTS = ISNULL(@PREV_MTH_END_TR_WIP,0)+ISNULL(@FT_SHIPMENTS_TO_TR_WIP,0)+ISNULL(@BULK_SHIPMENTS_TO_TR_WIP,0)-ISNULL(@TILL_MTH_END_TR_WIP,0)

	SELECT @ACTUAL_TR_SHIPMENTS_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P   
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Tape & Reel'
				AND SL.SL_NEXT_PROCESS <> 'Tape & Reel'
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	--FG
	SELECT @PREV_MTH_END_FG_WIP = SUM(QTY_IN) FROM #PREV_MTH_END_WIP_RESULTS WHERE (PS_TYPE = 'FG' OR PS_TYPE = 'Finished Goods') AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	SELECT @TILL_MTH_END_FG_WIP = SUM(QTY_IN) FROM #TILL_MTH_END_WIP_RESULTS WHERE (PS_TYPE = 'FG' OR PS_TYPE = 'Finished Goods') AND BASE_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	--FT Shipments to TR = @FT_SHIPMENTS_TO_FG_WIP
	SELECT @TR_SHIPMENTS_TO_FG_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P  
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND PS.PS_TYPE = 'Tape & Reel'
				AND SL.SL_NEXT_PROCESS = 'FG' 
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE
				AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR

	SET @TOT_FG_SHIPMENTS = ISNULL(@PREV_MTH_END_FG_WIP,0)+ISNULL(@FT_SHIPMENTS_TO_FG_WIP,0)+ISNULL(@TR_SHIPMENTS_TO_FG_WIP,0)-ISNULL(@TILL_MTH_END_FG_WIP,0)

	SELECT @SHIPMENTS_TO_CUSTOMER = SUM(CSLIT_QTY_SHIPPED) FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P
				WHERE CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID 
					AND CST.CST_STATUS = 'SHIPPED'
					AND COOL.LINEITEM_ID = CSLIT.LINEITEM_ID		
					AND P.PRODUCT_ID = COOL.PRODUCT_ID		
					AND CST.CST_CONFIRMED_ON > @PREV_MONTH_END_DATE
					AND CST.CST_CONFIRMED_ON <= @TILL_MONTH_END_DATE
					AND P.P_PARENT_PRODUCT_ID LIKE @BASE_PRODUCT_ID_STR
	--@FG_SHIPMENTS_TO_TR_WIP = @BULK_SHIPMENTS_TO_TR_WIP
	/*SELECT @FG_SHIPMENTS_TO_TR_WIP = SUM(SHIP_QTY) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS 
				WHERE LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'CLOSE' 
				AND PS.PS_TYPE = 'FG'
				AND SL.SL_NEXT_PROCESS = 'Tape & Reel' 
				AND SL.TIMESTAMP > @PREV_MONTH_END_DATE AND SL.TIMESTAMP <= @TILL_MONTH_END_DATE*/
	SET @ACTUAL_FG_SHIPMENTS = ISNULL(@SHIPMENTS_TO_CUSTOMER,0)+ISNULL(@FG_SHIPMENTS_TO_TR_WIP,0)

	INSERT INTO @RESULTS_TABLE ( DB_STARTING_WIP, FAB_SHIPMENTS_WIP, DB_ENDING_WIP, DB_TOT_DIE_MOVED_TO_ASSEMBLY_WIP, 
				ASM_STARTING_WIP, ASM_ENDING_WIP, ACTUAL_ASM_RECEIVINGS, TOT_ASM_SHIPMENTS, ACTUAL_ASM_SHIPMENTS, 
				FT_STARTING_WIP, ASM_SHIPMENTS_TO_FT, FT_ENDING_WIP, ACTUAL_FT_RECEIVINGS, TOT_FT_SHIPMENTS, FT_SHIPMENTS_TO_TR, FT_SHIPMENTS_TO_FG, ACTUAL_FT_SHIPMENTS, 
				TR_STARTING_WIP, FG_SHIPMENTS_TO_TR, TR_ENDING_WIP, TOT_TR_SHIPMENTS, ACTUAL_TR_SHIPMENTS, 
				FG_STARTING_WIP, TR_SHIPMENTS_TO_FG, FG_ENDING_WIP, TOT_FG_SHIPMENTS, SHIPMENTS_TO_CUSTOMERS, ACTUAL_FG_SHIPMENTS )
			VALUES ( @PREV_MTH_END_DB_WIP, @FAB_SHIPMENTS_WIP, @TILL_MTH_END_DB_WIP, @TOT_DIE_MOVED_TO_ASSEMBLY, 
				@PREV_MTH_END_ASSEMBLY_WIP, @TILL_MTH_END_ASSEMBLY_WIP, @ASSEMBLY_RECEIVINGS_WIP, @TOT_ASSEMBLY_SHIPMENTS, @ACTUAL_ASSEMBLY_SHIPMENTS_WIP,
				@PREV_MTH_END_FT_WIP, @ACTUAL_ASSEMBLY_SHIPMENTS_TO_FT_WIP, @TILL_MTH_END_FT_WIP, @ACTUAL_FT_RECEIVINGS_WIP, @TOT_FT_SHIPMENTS, @FT_SHIPMENTS_TO_TR_WIP, @FT_SHIPMENTS_TO_FG_WIP, @ACTUAL_FT_SHIPMENTS,
				@PREV_MTH_END_TR_WIP, @BULK_SHIPMENTS_TO_TR_WIP, @TILL_MTH_END_TR_WIP, @TOT_TR_SHIPMENTS, @ACTUAL_TR_SHIPMENTS_WIP, 
				@PREV_MTH_END_FG_WIP, @TR_SHIPMENTS_TO_FG_WIP, @TILL_MTH_END_FG_WIP, @TOT_FG_SHIPMENTS, @SHIPMENTS_TO_CUSTOMER, @ACTUAL_FG_SHIPMENTS )

	SELECT * FROM @RESULTS_TABLE
END